package cn.rengy.web.framework.sql;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.util.FileCopyUtils;

@Repository
public class Dao {
	private static Logger logger = LoggerFactory.getLogger(Dao.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	private LobHandler  lobHandler = new DefaultLobHandler();
	
	public <T> List<T> querySingleColumnForList(String sql, Class<T> elementType,Object... args){
		return this.jdbcTemplate.queryForList(sql,elementType,args);
	}
	public <T> List<T> queryNamedSingleColumnForList(String sql, SqlParameterSource sqlParameterSource, Class<T> elementType){
		return this.namedParameterJdbcTemplate.queryForList(sql,sqlParameterSource,elementType);
	}
	/**
	 * 通过name绑定参数
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public List<Map<String,Object>> queryNamedForList(String sql,SqlParameterSource paramSource){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(paramSource.toString());
			
		}
		return namedParameterJdbcTemplate.queryForList(sql, paramSource);
	}
	/**
	 * 查询
	 * 通过name绑定参数
	 * @param sql
	 * @param paramMap
	 * @return Object
	 */
	public <T> T queryNamedSingleColumn(String sql,SqlParameterSource sqlParameterSource,Class<T> requiredType){
		try{
			return namedParameterJdbcTemplate.queryForObject(sql, sqlParameterSource, new SingleColumnRowMapper<>(requiredType));
		}catch(EmptyResultDataAccessException e){}
		return null;
	}
	public int updateNamed(String sql,SqlParameterSource sqlParameterSource){
		return namedParameterJdbcTemplate.update(sql, sqlParameterSource);
	}
	public int update(String sql,Object... args){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(Arrays.toString(args));
			
		}
		return jdbcTemplate.update(sql, args);
		
	}
	/**
	 * 返回last_insert_id
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public Number updateNamedReturnGeneratedKeys(String sql,SqlParameterSource sqlParameterSource){
		KeyHolder keyHolder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql,sqlParameterSource, keyHolder);
		return keyHolder.getKey();
	}
	/**
	 * 返回last_insert_id
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public Number updateReturnGeneratedKeys(final String sql,final Object... args){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(Arrays.toString(args));
			
		}
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(
                    Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                new ArgumentPreparedStatementSetter(args).setValues(ps);
                return ps;
            }  
        }, keyHolder);
		return keyHolder.getKey();
	}
	public Map<String,Object> queryForMap(String sql,Object... args){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(Arrays.toString(args));
			
		}
		try{
			return jdbcTemplate.queryForMap(sql, args);
		}catch(EmptyResultDataAccessException e){
			
		}
		return null;
	}
	public Map<String,Object> queryNamedForMap(String sql,SqlParameterSource sqlParameterSource){
		try{
			return namedParameterJdbcTemplate.queryForMap(sql, sqlParameterSource);
		}catch(EmptyResultDataAccessException e){
			
		}
		return null;	
	}
	public <T> T querySingleColumn(String sql,Class<T> requiredType,Object... args){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(Arrays.toString(args));
			
		}
		try{
			return jdbcTemplate.queryForObject(sql, requiredType, args);
		}catch(EmptyResultDataAccessException e){}
		return null;
	}
	public <T> T queryForBean(String sql,Class<T> elementType,Object... args){
		try{
			return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(elementType), args);
		}catch(EmptyResultDataAccessException e){}
		return null;
		
	}
	public <T> T queryNamedForBean(String sql,SqlParameterSource sqlParameterSource,Class<T> elementType){
		try{
			return namedParameterJdbcTemplate.queryForObject(sql, sqlParameterSource, new BeanPropertyRowMapper<T>(elementType));
		}catch(EmptyResultDataAccessException e){
			return null;
		}
		
	}
	public List<Map<String,Object>> queryForList(String sql,Object... args){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(Arrays.toString(args));
			
		}
		return jdbcTemplate.queryForList(sql,args);
	}
	
	public <T> List<T> queryBeanForList(String sql, Class<T> elementType, Object... args){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(Arrays.toString(args));
			
		}
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(elementType), args);
	}
	public <T> List<T> queryNamedBeanForList(String sql, SqlParameterSource sqlParameterSource, Class<T> elementType){
		if(logger.isDebugEnabled()){
			logger.debug(sql);
			logger.debug(sqlParameterSource.toString());
			
		}
		return namedParameterJdbcTemplate.query(sql, sqlParameterSource, new BeanPropertyRowMapper<T>(elementType));
	}
	/**
	 * 批量更新
	 * @param sql
	 * @param batchArgs
	 * @return
	 */
	public int[] batchUpdate(String sql,List<Object[]> batchArgs){
		return jdbcTemplate.batchUpdate(sql, batchArgs);
	}
	/**
	 * namedParameter批量更新
	 * @param sql
	 * @param batchValues
	 * @return
	 */
	public int[] batchNamedUpdate(String sql,SqlParameterSource[] batchArgs){
		return this.namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);
	}
	
	public void updateBlobAsBinaryStream(String sql,int paramIndex,int contentLength,Object... args){
		//jdbcTemplate.update
		jdbcTemplate.execute(sql,
			new AbstractLobCreatingPreparedStatementCallback(lobHandler){
				public void setValues(PreparedStatement pstmt,LobCreator lobCreator) throws SQLException,DataAccessException{
					int argLength=args.length;
					int forLength=argLength+1;
					for (int i = 1; i < forLength; i++) {
						Object arg=args[i-1];
						if(paramIndex==i){
							lobCreator.setBlobAsBinaryStream(pstmt, paramIndex, (InputStream)arg, contentLength);
						}else{
							pstmt.setObject(i, arg);
						}
					}
				}
			}
		);
	}
	public void update2BlobAsBinaryStream(String sql,int paramIndex,int contentLength,int param2Index,int content2Length,Object... args){
		//jdbcTemplate.update
		jdbcTemplate.execute(sql,
			new AbstractLobCreatingPreparedStatementCallback(lobHandler){
				public void setValues(PreparedStatement pstmt,LobCreator lobCreator) throws SQLException,DataAccessException{
					int argLength=args.length;
					int forLength=argLength+1;
					for (int i = 1; i < forLength; i++) {
						Object arg=args[i-1];
						if(paramIndex==i||param2Index==i){
							lobCreator.setBlobAsBinaryStream(pstmt, paramIndex, (InputStream)arg, contentLength);
						}else{
							pstmt.setObject(i, arg);
						}
					}
				}
			}
		);
	}
	public void updateBlobAsBytes(String sql,Object... args){
		jdbcTemplate.execute(sql,
			new AbstractLobCreatingPreparedStatementCallback(lobHandler){
				public void setValues(PreparedStatement pstmt,LobCreator lobCreator) throws SQLException,DataAccessException{
					int argLength=args.length;
					int forLength=argLength+1;
					for (int i = 1; i < forLength; i++){
						Object arg=args[i-1];
						if(arg!=null&&arg.getClass()==byte[].class){
							lobCreator.setBlobAsBytes(pstmt, i, (byte[])arg);
						}else{
							pstmt.setObject(i, arg);
						}
					}
				}
			}
		);
	}
	public void getBlobAsBinaryStream(OutputStream outputStream,String sql,Object... args){
		jdbcTemplate.query(sql, args,
		 		 new AbstractLobStreamingResultSetExtractor(){
		 			 public void streamData(ResultSet rs) throws SQLException, IOException {
		 				 FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), outputStream);
		              }
		          }
		  );
	}
	public byte[] getBlobAsBytes(String sql,Object... args){
		return (byte[])jdbcTemplate.query(sql, args,
		 		 new ResultSetExtractor<byte[]>(){
		 			 public byte[] extractData(ResultSet rs) throws SQLException, DataAccessException {
		 				if (!rs.next()) {
		 					throw new EmptyResultDataAccessException(
		 							"LobStreamingResultSetExtractor did not find row in database", 1);
		 				}
		 				return lobHandler.getBlobAsBytes(rs, 1);
		 				 //FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), outputStream);
		              }
		          }
		  );
	}
	public Blob getBlob(String sql,Object... args){
		return (Blob)jdbcTemplate.query(sql, args,
		 		 new ResultSetExtractor<Blob>(){
		 			 public Blob extractData(ResultSet rs) throws SQLException, DataAccessException {
		 				if (!rs.next()) {
		 					throw new EmptyResultDataAccessException(
		 							"LobStreamingResultSetExtractor did not find row in database", 1);
		 				}
		 				return rs.getBlob(1);
		 				 //FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), outputStream);
		              }
		          }
		  );
	}
}
